<?php
/**
 * This class presents an abstraction layer between the standard PHP DB functions and our classes
 * @see \mysqli
 */
class MySQL extends mysqli {
	/**
	 * @var bool|mixed $dbHost The MySQL server URL
	 */
	private $dbHost;
	/**
	 * @var bool|mixed $dbUser The MySQL server username
	 */
	private $dbUser;
	/**
	 * @var bool|mixed $dbPassword The password for the MySQL user
	 */
	private $dbPassword;
	/**
	 * @var bool|mixed The name of the database
	 */
	private $dbName;
	/**
	 * @var MySQL Contains the last created instance of this class
	 */
	private static $dbLink;
	/**
	 * @var mixed Whether to store debug data. Takes the value from the one set in the Config class
	 */
	private $debug;
	/**
	 * @var \mysqli_result $lastResult Contains the result of the last query
	 */
	private $lastResult;

	/**
	 * @var string $lastQuery contains the last query
	 */
	public static $lastQuery;
	/**
	 * @var array If $this->debug evaluates to true, will contain all queries executed with this class
	 */
	public static $allQueries = array();
	/**
	 * @var int Contains the total number of queries ran through this class
	 */
	public static $queriesNr = 0;
	/**
	 * @var int Contains the total execution time of all the queries ran through this class
	 */
	public static $queriesTime = 0;


	/**
	 * Connects to the DB
	 * @param bool $dbHost The MySQL server URL. Optional. If not specified, defaults to the value set in Config class.
	 * @param bool $dbUser The MySQL server user. Optional. If not specified, defaults to the value set in Config class.
	 * @param bool $dbPassword The MySQL server password. Optional. If not specified, defaults to the value set in Config class.
	 * @param bool $dbName The MySQL database name. Optional. If not specified, defaults to the value set in Config class.
	 */
	function __construct($dbHost = false, $dbUser = false, $dbPassword = false, $dbName = false) {
		$this->debug = conf('DEBUG');
		$this->dbHost = $dbHost ? $dbHost : conf('DB_HOST');
		$this->dbUser = $dbUser ? $dbUser : conf('DB_USER');
		$this->dbPassword = $dbPassword ? $dbPassword : conf('DB_PASSWORD');
		$this->dbName = $dbName ? $dbName : conf('DB_NAME');
		parent::__construct($this->dbHost, $this->dbUser, $this->dbPassword, $this->dbName);
		self::$dbLink = &$this;
		if ($this->connect_errno) {
			trigger_error('Failed to connect to MySQL: ' . $this->connect_error, E_USER_ERROR);
			exit;
		}
		ini_set('mbstring.internal_encoding', 'UTF-8');
		ini_set('mbstring.http_input', 'UTF-8');
		ini_set('mbstring.http_output', 'UTF-8');
		if (!$this->set_charset('utf8')) {
			trigger_error('Error loading character set utf8: ' . $this->error, E_USER_ERROR);
			exit;
		}
	}

	/**
	 * Escapes a string, that is, prevents SQL injections and encodes the data according to the DB/table settings
	 * @static
	 * @used-by \MySQL::escapeValue($value,$trim)
	 * @param string $string The string to escape
	 * @return string The escaped string
	 */
	public static function escapeString($string) {
		/**
		 * mysqli_real_escape_string returns actually a string. phpStorm has a bug when inspecting it.
		 */
		return mysqli_real_escape_string(self::$dbLink, $string);
	}

	/**
	 * Escape string according to MySQL syntax.
	 *
	 * @static
	 * @uses \MySQL::escapeString($string)
	 * @param array|int|float|string $value The value to escape. If the value is an array, the function will call itself
	 * on every element and then return a string of the escaped elements separated by commas, i.e. <code>array(1, 2, 3, 4)
	 * => "'1', '2', '3', '4'"</code>
	 * If the value is <b>null</b>, the function will return <b>null</b>. If it's a number or string, the function will encaps it in
	 * single quotes, i.e. <code>3 => '3'</code>. If all of the above fails, the function will cast the argument to (int) and return it
	 * @param bool $trim Optional. Whether to trim the argument if it's a string, that is, whether to remove the spaces at the beginning and at the end of a string. Defaults to true.
	 * @return string|int The escaped value OR the first argument casted to (int) if the function has no condition case
	 * for it OR <b>null</b> if the argument is <b>null</b>
	 */
	public static function escapeValue($value, $trim = true) {
		if (is_array($value)) {
			foreach ($value as $key => $val) {
				$string[$key] = self::escapeValue($val, $trim);
			}
			$string = join(',', $value);
		} else {
			if (is_string($value)) {
				if ($trim) {
					return "'" . trim(self::escapeString($value)) . "'";
				}
				return "'" . self::escapeString($value) . "'";
			}
			if (is_numeric($value)) return "'" . $value . "'";
			if (is_null($value)) return 'NULL';
			return (int)$value;
		}

		return $string;
	}

	/**
	 * Replaces placeholders in an SQL query with actual values.
	 * Placeholders: will be replaced according to order by corresponding $args[$i],
	 *		"?"  => string - escaped and put in apostrophes.
	 *		"?d" => integer
	 *		"?n" => string - same as ?, but can be NULL
	 *		"?l" => string - for use in LIKE statement
	 * 		"?f" => string - a MySQL table name of field name. Will be encapsed in backticks. I.e. "myTable" => "`myTable`"
	 *
	 * <code>$args => array($arg1, $arg2, $arg3, $argn...)</code>
	 *
	 * @uses \MySQL::escapeValue($value,$trim)
	 * @used-by \MySQL::&exec($args,$storeResult)
	 * @param string $query A MySQL query string, containing placeholders.
	 * @param array $args An array with values for placeholders. If the array is empty, the function will return the initial
	 * MySQL query string, no modifications being made to it.
	 * @return string Returns the query string with placeholder being replaced with values
	 */
	private function replacePlaceholders($query, $args) {
		if (count($args) > 0) {
			$ex = explode('?', $query);
			$query = '';
			foreach ($ex as $k => $v) {
				if ($k > 0) {
					$i = $k - 1;
					$mod = isset ($v[0]) ? $v[0] : false;
					switch ($mod) {
						case 'd' : // NUMERICAL
							if (@ is_numeric($args[$i])) {
								$query .= $args[$i];
								$v = substr($v, 1);
							} elseif (@ is_array($args[$i]) && @ array_walk($args[$i], 'is_numeric')) {
								$query .= join(', ', $args[$i]);
								$v = substr($v, 1);
							} else
								$query .= '?';
							break;
						case 'n' : // NULLABLE STRING FIELD
							if (array_key_exists($i, $args)) {
								if (is_null($args[$i]))
									$query .= 'NULL';
								else
									$query .= $this->escapeValue($args[$i]);
								$v = substr($v, 1);
							} else
								$query .= '?';
							break;
						case 'i' : // INT
							if (isset ($args[$i])) {
								$query .= (int)$args[$i];
								$v = substr($v, 1);
							} else
								$query .= '?';
							break;
						case 'l' : // STRING FIELD FOR USE IN <LIKE> STATEMENT
							if (isset ($args[$i])) {
								$query .= $this->escapeValue('%' . str_replace('%', "\\%", $args[$i]) . '%');
								$v = substr($v, 1);
							} else
								$query .= '?';
							break;
						case 'f': // FIELD NAME ---- ESCAPE WITH '``'
							if (isset ($args[$i])) {
								$query .= '`' . $args[$i] . '`';
								$v = substr($v, 1);
							} else
								$query .= '?';
							break;
						default : // NOT-NULLABLE STRING FIELD
							if (isset ($args[$i])) {
								if (@ is_array($args[$i])) {
									$argArray = array();
									foreach ($args[$i] as $arg) {
										$argArray[] = $this->escapeValue($arg);
									}
									$query .= join(', ', $argArray);
								} else {
									$query .= $this->escapeValue($args[$i]);
								}
							} else {
								$query .= '?';
							}
							break;
					}
				}
				$query .= $v;
			}
		}
		return $query;
	}

	/**
	 * Executes a query. Stores the last result and the last query, accumulates <code>$this->allQueries, $this->queriesNr
	 * and $this->queriesTime</code>. Logs the slow queries(that take more than 0.5 seconds to execute)
	 * <b>DO NOT</b> change this function from private to public nor call this function directly!
	 * Use the helper functions instead (q(), select(), etc.)
	 * @see \MySQL::q($storeResult,...)
	 * @see \MySQL::qArgs($args,$storeResult)
	 * @see \MySQL::select(...)
	 * @see \MySQL::selectRow(...)
	 * @see \MySQL::selectCol(...)
	 * @see \MySQL::selectCell(...)
	 * @see \ct($module,$key)
	 * @param array $args Array of arguments
	 * @param bool $storeResult Whether to return the last result(store it) or keep it in $this->lastResult. It is necessary to pass
	 * this parameter as true when you need a second query and the data from the first query has not been fetched yet.
	 * For example, such situations may appear when you do a <code>->fetch_assoc()</code> cycle and you use the <code>ct</code> function inside it.
	 * Optional. Defaults to false
	 * @return bool|mysqli_result The result on success and false on failure
	 */
	private function &exec($args, $storeResult = false) {
		/**
		 * We remove the first element from array because it contains the $sql query itself.
		 */
		$sql = array_shift($args);
		$sql = $this->replacePlaceholders($sql, $args);

		/**
		 * Just to make the inspector happy
		 */
		$lastResult = true;

		self::$lastQuery = $sql;
		if (conf('DEBUG')) {
			self::$allQueries[] = $sql;
		}
		$this->clearResult();
		$startTime = microtime(true);
		if ($storeResult) {
			$lastResult = $this->query($sql);
		} else {
			$this->lastResult = $this->query($sql);
		}
		$endTime = microtime(true);

		$queryTime = $endTime - $startTime;

		self::$queriesTime += $queryTime;
		self::$queriesNr++;

		if ($queryTime >= 0.5) {
			Engine::$LOG->write('Time: ' . $queryTime . "\n" . self::$lastQuery, 'mysql_slow_queries');
		}

		if ($storeResult) {
			if (!$lastResult) {
				trigger_error('Error executing last SQL query: ' . $this->error . "\n" . '&quot;' . self::$lastQuery.'&quot;', E_USER_ERROR);
				exit;
			}
			return $lastResult;
		}
		if (!$this->lastResult) {
			trigger_error('Error executing last SQL query: ' . $this->error . "\n" . '&quot;' . self::$lastQuery . '&quot;', E_USER_ERROR);
			exit;
		}
		return $this->lastResult;
	}

	/**
	 * Closes the connection and frees the result. Sets <code>$this->lastResult</code> to <b>null</b>
	 */
	private function clearResult() {
		if ($this->lastResult instanceof mysqli_result) {
			$this->lastResult->close();
		}
		$this->lastResult = null;
	}

	/**
	 * Function to execute a query for INSERT, UPDATE, DELETE, etc.
	 * You can use it for SELECT queries only if you know how to deal correctly with mysqli_result objects:
	 * http://sg.php.net/manual/ru/class.mysqli-result.php
	 * Set the $storeResult to true only in case you're going to run mysql queries inside a fetch_* while loop.
	 * In general it is not a good practice to do this but in case you will, you have to take care that
	 * the result is freed and also take in account that most of the other functions from this class will
	 * not work with it.
	 *
	 * <b>Example:</b>
	 * <code>
	 * <?php<br>
	 * $DB = new MySQL();<br>
	 * $storedMySqlResult = $DB->q(true, $sqlQuery, $placeholderValue1, $placeholderValue2, $placeholderValue3);<br>
	 * $resultCount = $DB->numRows(); // this will NOT work<br>
	 * $resultCount = $storedMySqlResult->num_rows; // this will work<br>
	 * while ($row = $storedMySqlResult->fetch_assoc()) {<br>
	 * 		$DB->q($sqlQuery2, $placeholderValueX, ...);<br>
	 * 		$secondResultCount = $DB->numRows(); // this works fine<br>
	 * }<br>
	 * $storedMySqlResult->close();<br>
	 * </code>
	 * @param bool|string $storeResult Set it to true only in case you're going to run mysql queries inside a fetch_* while loop.
	 * @return bool|mysqli_result <b>False</b> on failure, the query result on success
	 */
	public function &q($storeResult = false) {
		$args = func_get_args();
		if (is_bool($storeResult)) {
			array_shift($args);
		} else {
			$storeResult = false;
		}
		return $this->exec($args, $storeResult);
	}

	/**
	 * Executes a query taking the arguments as array
	 * @uses \MySQL::exec($args,$storeResult)
	 * @param array $args the array with arguments: 0=>the SQL query, 1,2,3=>Values for placeholders
	 * @param bool $storeResult Whether to return the last result(store it) or keep it in $this->lastResult. It is necessary to pass
	 * this parameter as true when you need a second query and the data from the first query has not been fetched yet.
	 * For example, such situations may appear when you do a <code>->fetch_assoc()</code> cycle and you use the <code>ct</code> function inside it.
	 * Optional. Defaults to false
	 * @return bool|mysqli_result <b>False</b> on failure, the query result on success
	 */
	public function &qArgs($args, $storeResult = false) {
		return $this->exec($args, $storeResult);
	}

	/**
	 * Returns all the tables in the current DB
	 * @uses \MySQL::selectCol(...)
	 * @return mixed An 1-dimension array containing the names of all the tables in the DB as strings
	 */
	public function showTables() {
		return $this->selectCol("SHOW TABLES;");
	}

	/**
	 * Returns the number of affected(modified, affected) rows. Useful when dealing with UPDATE or DELETE queries.
	 * @return int The number of rows that were affected by the last query
	 */
	public function affectedRows() {
		return $this->affected_rows;
	}

	/**
	 * Uses the affected rows or the last query info to fetch the result of last query modified rows
	 * @uses $this->info
	 * @uses \MySQL::affectedRows()
	 * @return int The number of matched rows if available. 0 if no info is available
	 */
	public function mysqlModifiedRows() {
		$infoStr = $this->info;
		$affectedRows = $this->affectedRows();
		preg_match("/Rows matched: ([0-9]*)/", $infoStr, $rowsMatched);
		return ($affectedRows < 1) ? ($rowsMatched[1] ? $rowsMatched[1] : 0) : $affectedRows;
	}

	/**
	 * Returns the number of selected rows.
	 * @return bool|int <b>False</b> on failure(if now lastResult exists), the number of selected rows on success
	 */
	public function numRows() {
		if ($this->lastResult instanceof mysqli_result) {
			return $this->lastResult->num_rows;
		} else {
			return false;
		}
	}

	/**
	 * Gets the last id inserted in DB. Useful when dealing with inserts in tables with autoincrement fields
	 * @return int The last inserted id
	 */
	public function lastId() {
		return $this->insert_id;
	}

	/**
	 * Returns the max id(primary key) in a table or a set of entries is a table.
	 * @uses \MySQL::selectCell(...)
	 * @param string $tableName The name of the table
	 * @param string|array|bool $where An SQL conditional expression <code>`foo` = 'bar'</code> or a hash array, with the
	 * keys being the names of table fields and values being those fields' values to match. Optional. Defaults to <b>
	 * false,</b> meaning no filtering.
	 * @param string $condition An SQL logic operator, like <code>AND, OR or OR NOT.</code> Optional. Defaults to <code>
	 * AND</code>
	 * @param string $idField The id(primary key) field of the table. Optional. Defaults to <code>`id`</code>
	 * @return bool|int <b>False</b> if the table name is empty, the maxId if the query was successful
	 */
	public function maxId($tableName, $where = false, $condition = 'AND', $idField = 'id') {
		if (!empty($tableName)) {
			$whereAdd = '';
			if (is_string($where) && !empty($where)) {
				$whereAdd = ' WHERE '.$where;
			} else if (is_array($where) && count($where) > 0) {
				$whereAdd = ' WHERE ';
				$whereAddArr = array();
				foreach ($where as $field => $value) {
					$whereAddArr[] = ' '.$field.'='.$value.' ';
				}
				$whereAdd .= implode($condition, $whereAddArr);
			}
			return $this->selectCell("SELECT MAX(?f) FROM ?f{$whereAdd};", $idField, $tableName);
		} else {
			return false;
		}
	}

	/**
	 * Locks a table to prevent writing
	 * @uses \MySQL::q(...)
	 * @param string $tableName The name of the table.
	 */
	public function lockTable($tableName) {
		if (!empty($tableName)) {
			$this->q("LOCK TABLE ?f WRITE;", $tableName);
			$this->autocommit(false);
		}
	}

	/**
	 * Unlocks all tables
	 */
	public function unlockTables() {
		$this->autocommit(true);
		$this->query("UNLOCK TABLES;");
	}

	/**
	 * Returns the code of the last query
	 * @uses \MySQL::$lastQuery
	 * @return string The last query
	 */
	public static function getLastQuery() {
		return self::$lastQuery;
	}

	/**
	 * Execute a sql query and return the result as an bi-dimensional array
	 * The first parameter should always be the SQL query itself and the next parameters - value for placeholders
	 *
	 * @return mixed Result array on success and FALSE on failure
	 */
	public function select() {
		$args = func_get_args();
		$this->exec($args);

		if ($this->numRows() > 0) {
			for ($res = array(); $tmp = $this->lastResult->fetch_assoc();) {
				$res[] = $tmp;
			}
			$this->clearResult();
			return $res;
		} else {
			return false;
		}
	}

	/**
	 * Execute a sql query and return one selected row as an array
	 * The first parameter should always be the SQL query itself and the next parameters - value for placeholders
	 *
	 * @return mixed Result array on success and FALSE on failure
	 */
	public function selectRow() {
		$args = func_get_args();
		$this->exec($args);

		if ($this->numRows() > 0) {
			$result = $this->lastResult->fetch_assoc();
			$this->clearResult();
			return $result;
		} else {
			return false;
		}
	}

	/**
	 * Return an array with values from a specified column<br>
	 *	<b>Note:</b>
	 *	- in the query string must be specified only one field. ex:
	 *		 <code>SELECT `name` from users;<code>
	 * The first parameter should always be the SQL query itself and the next parameters - value for placeholders
	 *
	 * @return mixed Array on success and FALSE on failure
	 */
	public function selectCol() {
		$args = func_get_args();
		$this->exec($args);

		$resultArray = array();
		if ($this->numRows() > 0) {
			while ($row = $this->lastResult->fetch_array(MYSQLI_NUM)) {
				$resultArray[] = $row[0];
			}
			$this->clearResult();
			return $resultArray;
		} else {
			return false;
		}
	}

	/**
	 * Select only one cell
	 *
	 * @return mixed String on success and FALSE on failure
	 */
	public function selectCell() {
		$args = func_get_args();

		$this->exec($args);
		if ($this->numRows() > 0) {
			$row = $this->lastResult->fetch_array(MYSQLI_NUM);
			$this->clearResult();
			return $row[0];
		} else {
			return false;
		}
	}

	/**
	 * Make pagination
	 *
	 * Result is an array with mysql query limit as first value and the pagination array as second value
	 *
	 * @deprecated
	 * @param string $sql
	 * @param integer $cp Current page number
	 * @param mixed $rpp If is not null must be integer (mean results per page)
	 * @return array
	 */
	public function getPagination($sql, $cp=1, $rpp=NULL) {

		$returnSet = array();

		$this->q($sql);
		$totalElements = $this->numRows();
		if ((int)$rpp <= 0) {
			// If the "results per page" variable wasn't set, assign to it the RESULTS_PER_PAGE config value
			$rpp = conf('RESULTS_PER_PAGE');
		}

		$totalPages = ceil($totalElements/$rpp);
		if (!$cp || (int)$cp > $totalPages) {
			// If the current page is not set or it's bigger than total number of pages, assign to it 1
			$cp = 1;
		}

		$returnSet[0] = " LIMIT ".(($cp-1)*$rpp).", ".$rpp;  // [0] = limit 0,5 ; 5,5 ; 10,5...

		$returnSet[1] = array();

		for ($i=1;$i<=$totalPages;$i++) {
			$returnSet[1][$i] = $i;// [1] [1 2 3 4 5 6 ... ] = 1 2 3 4 5 6 ...
		}

		return $returnSet;
	}

     /**
	 * Generates an insert string based on the data you supply, and runs the query.
	 * <b>Example:</b><br>
	 * <code>
	 * $data = array(
	 *	 array(
	 *	 'name' => 'My Name 2' ,
	 *	 'date' => 'My date 2'
	 *	 ),
	 *	 array(
	 *	 'name' => 'Another Name 2' ,
	 *	 'date' => 'Another date 2'
	 *	 )
	 *	 );
	 * </code>
	 * <b>Usage:</b><code>self::$DB->insertBatch($tableName, $data [, bool $duplicateUpdate]);</code>
	 *
	 * @param string $tableName The first parameter will contain the table name
	 * @param array $data Associative array of values
     * @param bool $duplicateUpdate Suppose the table has a unique field or combination of fields, and the object you
	 * are trying to save does not respect this uniqueness. If this parameter is set to true, the existing field will be
	 * overwritten by the one being saved, otherwise, the saving will fail. Optional. Defaults to <b>false</b>
     * @return boolean TRUE on success and FALSE on failure
	 */
    public function insertBatch($tableName, $data, $duplicateUpdate = false) {

        $resp = false;

        if (is_array($data) && count($data)) {
            $parts = array(
                "fields" => array(),
                "values" => array()
            );

            $i = 0;
            foreach ($data AS $arr) {
                $set = array();
                foreach ($arr AS $key => $val) {
                    if ($i == 0) {
                        $parts['fields'][] = '`'.$key.'`';
                    }
                    $set[] = $this->escapeValue($val);
                }
                $parts['values'][] = '('.implode(', ', $set).')';

                $i++;
            }

            $sql = "INSERT INTO ?f (".implode(', ',$parts['fields']).") VALUES ".implode(', ', $parts['values']);

            if ($duplicateUpdate) {
                $onDuplicateStatement = array();
                foreach ($parts['fields'] AS $field) {
                    $onDuplicateStatement[] = $field . ' = VALUES('.$field.')';
                }

                $sql .= " ON DUPLICATE KEY UPDATE ".implode(', ', $onDuplicateStatement);
            }

            if ($this->q($sql, $tableName)) {
                $resp = true;
            }
        }

		return $resp;
    }

    /**
	 * Generates an update string based on the data you supply, and runs the query.
	 * <b>Example:</b>
	 * <code>
	 * $data = array(
	 *	array(
 	 *	'id' => '1' ,
	 *	'name' => 'My Name 2' ,
	 *	'date' => 'My date 2'
	 *	),
	 *	array(
	 *	'id' => '2' ,
	 *	'name' => 'Another Name 2' ,
	 *	'date' => 'Another date 2'
	 *	)
	 *	);
	 * </code>
     * @param string $tableName The first parameter will contain the table name
	 * @param array $data The second parameter is an associative array of values
	 * @param string $idField The third parameter is the where key
     * @return boolean TRUE on success and FALSE on failure
     * <b>Usage:</b><code> self::$DB->updateBatch($tableName, $data [, $whereKey]);
	 *	$whereKey can be 'id' or 'title' or 'date'</code>
	 */
    public function updateBatch($tableName, $data, $idField = 'id') {

        $resp = false;

        if (is_array($data) && (is_string($idField) && !empty($idField))) {

            $parts = array();
            $ids = array();

            $part = 0;
            $count = 0;

            foreach ($data AS $arr) {
                foreach ($arr AS $key => $val) {
                    if ($key != $idField) {
                        $val = $this->escapeValue($val);

                        $parts[$part][$key]['begin'] = " `{$key}` = CASE ";
                        $parts[$part][$key]['end'] = " ELSE `{$key}` END ";
                        $parts[$part][$key]['item'][] = "WHEN `{$idField}` = '{$arr[$idField]}' THEN {$val}";

                        $ids[$part][$arr[$idField]] = $arr[$idField];
                    }
                }
                $count++;

                $part = floor($count / 100);
            }

            if (count($parts)) {
                foreach ($parts AS $index => $arr) {
                    $i = 1;
                    $set = '';
                    $j = count($arr);
                    foreach ($arr AS $key) {
                        $set .= $key['begin'];
                        foreach ($key['item'] AS $item) {
                            $set .= $item.' ';
                        }
                        $set .= $key['end'].($i < $j ? ',' : '');
                        $i++;
                    }

                    $sql = "UPDATE ?f SET {$set} WHERE `{$idField}` IN ('".implode("','",$ids[$index])."')";
                    if ($this->q($sql, $tableName)) {
                        $resp = true;
                    } else {
                        return false;
                    }
                }
            }
        }

        return $resp;
    }

	/**
	 * Closes the connection
	 */
	function __destruct() {
		$this->close();
	}
}
